CREATE PROCEDURE sp_asi_GetCounter  
/***   sp_asi_GetCounter
For a given CounterName, returns the next value. (Guaranteed unique).
If you supply the optional second param "incrementBy", then you receive the additional guarantee that
   you can use that many consecutive numbers, all with a uniqueness guarantee.
Sample use:  ----------------------------------------------------------------
    exec sp_asi_GetCounter  'MyCounterName', 3
  returns
     101
  Now you know that 101, 102, 103 are all usable unique numbers
********/
  @CounterName varchar(30) = '' ,
  @IncrementBy   int = 1,
  @ChecksumOnOff int = 0 /* 1 = ON, 2 = OFF */
  AS
  if not exists (select COUNTER_NAME from Counter where COUNTER_NAME=@CounterName)
         insert Counter (COUNTER_NAME, LAST_VALUE)
         values (@CounterName,0)
declare @id varchar(10),@NewId varchar(10), @LastValue int,@checksum int,@strChecksum varchar(1),
@digit int,@weight int,@subpos int,@sum int,@weights varchar(9),@pos int
if @ChecksumOnOff=1
BEGIN
   select @LastValue=LAST_VALUE from Counter where COUNTER_NAME='Name'
   select @id=convert(varchar(10),@LastValue)
   select @id=substring(@id,1,datalength(@id)-1)
   select @LastValue=convert(int,@id+1)*10
   update Counter set LAST_VALUE=@LastValue , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=1
   where COUNTER_NAME = 'Name'
   select @LastValue=LAST_VALUE from Counter where COUNTER_NAME='Prospect'
   select @id=convert(varchar(10),@LastValue)
   select @id=substring(@id,1,datalength(@id)-1)
   select @LastValue=convert(int,@id+1)*10
   update Counter set LAST_VALUE=@LastValue , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=1
   where COUNTER_NAME = 'Prospect'
   return
END
if @ChecksumOnOff=2
BEGIN
   select @LastValue=LAST_VALUE from Counter where COUNTER_NAME='Name'
   select @id=convert(varchar(10),@LastValue)
   select @id=substring(@id,1,datalength(@id)-1)
   select @LastValue=convert(int,@id+1)*10
   update Counter set LAST_VALUE=@LastValue , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=0
   where COUNTER_NAME = 'Name'
   select @LastValue=LAST_VALUE from Counter where COUNTER_NAME='Prospect'
   select @id=convert(varchar(10),@LastValue)
   select @id=substring(@id,1,datalength(@id)-1)
   select @LastValue=convert(int,@id+1)*10
   update Counter set LAST_VALUE=@LastValue , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=0
   where COUNTER_NAME = 'Prospect'
   return
END
if @CounterName in ('Name','Prospect') and
(select ShortValue from System_Params where ParameterName='Member_Control.UseChecksumForId')='YES'
BEGIN
  select @weights='2345672345'
  select @LastValue=LAST_VALUE from Counter where COUNTER_NAME=@CounterName
  /* remove last digit if checksum is to be applied */
  if (select HAS_CHECKSUM from Counter where COUNTER_NAME=@CounterName)=1
  BEGIN
   select @id=convert(varchar(10),@LastValue)
   select @id=substring(@id,1,datalength(@id)-1)
   select @LastValue=convert(int,@id)+1
   select @id=convert(varchar(10),@LastValue)
  END
  ELSE
  BEGIN
   select @LastValue=@LastValue+1
   select @id=convert(varchar(10),@LastValue)
  END
  select @subpos=datalength(@id)+1
  select @pos=0
  while @pos<datalength(@id)
  BEGIN
   select @pos=@pos+1
   select @weight=convert(int,(substring(@weights,@pos,1)))
   select @digit=convert(int,(substring(@id,@subpos-@pos,1)))
   select @sum=isnull(@sum,0)+(@weight*@digit)
  END
  select @checksum=@sum%11
  if @checksum=0
  BEGIN
   select @checksum=1
  END
  select @checksum=(11-@checksum)%10
  select @strChecksum=convert(varchar(1),@checksum)
  select @NewId=@id+@strChecksum
   update Counter
   set LAST_VALUE=convert(int,@NewId) , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=1
   where COUNTER_NAME= @CounterName
   select LAST_VALUE from Counter where COUNTER_NAME= @CounterName
END
ELSE
BEGIN
  update Counter
  set LAST_VALUE=LAST_VALUE +@IncrementBy , LAST_UPDATED=getdate(), UPDATED_BY=user_name()
  where COUNTER_NAME= @CounterName
  select LAST_VALUE=LAST_VALUE - @IncrementBy +1
  from Counter where COUNTER_NAME= @CounterName
END

GO
GRANT EXECUTE ON  [dbo].[sp_asi_GetCounter] TO [IMIS]
GO